﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.WindowsAzure.ServiceRuntime;

using Domain;

namespace Repositories
{
	public class PostingRepository
	{
		string connStr;

		public SqlConnection Connection { get; set; }

		public SqlConnection CreateConnection()
		{
			connStr = RoleEnvironment.GetConfigurationSettingValue("ConnectionString");

			Connection = new SqlConnection(connStr);

			return Connection;
		}

		public void BeginTransaction()
		{
			Connection.BeginTransaction();
		}

		public PostingRepository()
		{
			CreateConnection();
		}

		public IList<Post> getPostings(Int64 topicID, Int16 page, Int16 pageSize, string userID, ref string currentUserRole)
		{
			List<Post> postings = new List<Post>();

			try
			{
				SqlConnection conn = new SqlConnection(connStr);
				SqlCommand cmd = new SqlCommand();
				cmd.Connection = conn;
				cmd.CommandText = "GetPosts";
				cmd.CommandType = CommandType.StoredProcedure;

				cmd.Parameters.Add(new SqlParameter("topicid", topicID));
				cmd.Parameters.Add(new SqlParameter("pageNr", page));
				cmd.Parameters.Add(new SqlParameter("pagesize", pageSize));

				if (userID != "")
				{
					System.Guid g = new Guid(userID);
					cmd.Parameters.Add(new SqlParameter("userID", SqlDbType.UniqueIdentifier, 99, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, g));
				}
				else
				{
					cmd.Parameters.Add(new SqlParameter("userID", SqlDbType.UniqueIdentifier, 99, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, null));
				}

				conn.Open();

				SqlDataReader dr = cmd.ExecuteReader();

				while (dr.Read())
				{
					Post p = new Post();

					p.ID = Int64.Parse(dr["PostingID"].ToString());
					p.TopicTitle = dr["title"].ToString();
					p.UrlTitle = dr["urlTitle"].ToString();
					p.Content = dr["Content"].ToString();
					p.PostedOn = dr["Timestamp"].ToString();
					p.Username = dr["Username"].ToString();
					p.UserAvatar = dr["Avatar"].ToString();
					p.UserSubtitle = dr["Subtitle"].ToString();
					p.UserSignature = dr["Signature"].ToString();
					p.UserTwitter = dr["Twitter"].ToString();
					p.UserLevelName = dr["LevelName"].ToString();
					p.SubforumName = dr["subforumName"].ToString();
					p.UserID = dr["UserID"].ToString();

					if (dr["order"].ToString() != "")
					{
						p.Order = Int64.Parse(dr["order"].ToString());
					}


					if (dr["subforumID"].ToString() != "")
					{
						p.SubforumID = Int64.Parse(dr["subforumID"].ToString());
					}

					if (dr["TotalCount"].ToString() != "")
					{
						p.TopicPostCount = Int64.Parse(dr["TotalCount"].ToString());
					}

					postings.Add(p);
				}

				dr.NextResult();
				while (dr.Read())
				{
				   currentUserRole = dr["role"].ToString();
				}

				conn.Close();
				conn.Dispose();
			}
			catch (SqlException ex)
			{
				if (ex.ErrorCode == -2146232060)
				{
					//Know bug where SQL Server has closed the connection, but the connection is still available in the asp.net connection pool
					Connection.Close();
					Connection.Dispose();
					getPostings(topicID, page, pageSize, userID, ref currentUserRole);
				}
				else
				{
					throw ex;
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return postings;

		}




		public DataSet getPost(Int64 postID)
		{
			SqlConnection con = new SqlConnection(connStr);

			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand = new SqlCommand("GetPost", con);
			da.SelectCommand.CommandType = CommandType.StoredProcedure;
			da.SelectCommand.Parameters.Add(new SqlParameter("postid", postID));

			DataSet dst = new DataSet();

			Connection.Open();
			da.Fill(dst);
			Connection.Close();
			Connection.Dispose();

			return dst;
		}
		


		/// <summary>
		/// Insert a posting into the database
		/// This method can be called if you want to use a transaction
		/// TODO: easier to refactor the other InsertPosting method to also work when "transaction == null"
		/// </summary>
		/// <param name="content"></param>
		/// <param name="topicID"></param>
		/// <param name="username"></param>
		/// <param name="userID"></param>
		public void InsertPosting(string content, Int64 topicID, string username, string userID, string ipaddress, out string postID, out int postCount)
		{
			SqlCommand cmd = null;
			postID = "";
			postCount = 0;

			cmd = new SqlCommand("InsertPost", Connection);

			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add(new SqlParameter("content", content));
			cmd.Parameters.Add(new SqlParameter("topicID", topicID));
			if (userID == "")
			{
				cmd.Parameters.Add(new SqlParameter("userid", null));
			}
			else
			{
				cmd.Parameters.Add(new SqlParameter("userid", userID));
			}
			
			cmd.Parameters.Add(new SqlParameter("username", username));

			cmd.Parameters.Add(new SqlParameter("ipaddress", ipaddress));

			SqlParameter parStateCode = new SqlParameter("state_code", 0);
			parStateCode.Direction = ParameterDirection.Output;
			cmd.Parameters.Add(parStateCode);

			SqlParameter parPostID = new SqlParameter("postID", 0);
			parPostID.Direction = ParameterDirection.Output;
			cmd.Parameters.Add(parPostID);

			SqlParameter parPostCount = new SqlParameter("postcount", 0);
			parPostCount.Direction = ParameterDirection.Output;
			cmd.Parameters.Add(parPostCount);

			if (Connection.State != ConnectionState.Open)
			{
				Connection.Open();
			}

			try
			{
				cmd.ExecuteNonQuery();
				Connection.Close();
				Connection.Dispose();

				postID = cmd.Parameters["postID"].Value.ToString();

				postCount = int.Parse(cmd.Parameters["postCount"].Value.ToString());
			}
			catch (SqlException ex)
			{
				if (ex.Number == 515) //-2146232060)
				{
				}
				else
				{
					//Know bug where SQL Server has closed the connection, but the connection is still available in the asp.net connection pool
					Connection.Close();
					Connection.Dispose();
					InsertPosting(content, topicID, username, userID, ipaddress, out postID, out postCount);
				}
				//else
				//{
				//   throw ex;
				//}
			}
			catch (Exception ex)
			{
				throw ex;
			}

			if (cmd.Parameters["state_code"].Value.ToString() != "0")
			{
				if (cmd.Parameters["state_code"].Value.ToString() == "1")
				{
					throw new Exception("This username is already registered.");
				}
				if (cmd.Parameters["state_code"].Value.ToString() == "2")
				{
					throw new Exception("The message has been recognized to be spam.");
				}
				else
				{
					throw new Exception("Unknow statuscode: " + cmd.Parameters["state_code"].Value.ToString());
				}
			}
		}


		/// <summary>
		/// Insert a posting into the database
		/// This method can be called if you want to use a transaction
		/// </summary>
		/// <param name="content"></param>
		/// <param name="topicID"></param>
		/// <param name="username"></param>
		/// <param name="userID"></param>
		/// <param name="connection"></param>
		/// <param name="transaction"></param>
		public void InsertPosting(string content, Int64 topicID, string username, string userID, string ipaddress, SqlConnection connection, SqlTransaction transaction)
		{
			SqlCommand cmd = new SqlCommand("InsertPost", connection);
			cmd.Transaction = transaction;

			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add(new SqlParameter("content", content));
			cmd.Parameters.Add(new SqlParameter("topicID", topicID));
			cmd.Parameters.Add(new SqlParameter("userid", userID));
			cmd.Parameters.Add(new SqlParameter("username", username));
			cmd.Parameters.Add(new SqlParameter("ipaddress", ipaddress));

			SqlParameter parStateCode = new SqlParameter("state_code", 0);
			parStateCode.Direction = ParameterDirection.Output;
			cmd.Parameters.Add(parStateCode);


			SqlParameter parPostID = new SqlParameter("postID", 0);
			parPostID.Direction = ParameterDirection.Output;
			cmd.Parameters.Add(parPostID);

			SqlParameter parPostCount = new SqlParameter("postcount", 0);
			parPostCount.Direction = ParameterDirection.Output;
			cmd.Parameters.Add(parPostCount);

			//Connection.Open();

			try
			{
				cmd.ExecuteNonQuery();
				//Connection.Close();
			}
			catch (Exception e)
			{
				cmd.Transaction.Rollback();
				//Connection.Close();

				throw e;
			}

			if (cmd.Parameters["state_code"].Value.ToString() != "0")
			{
				if (cmd.Parameters["state_code"].Value.ToString() == "1")
				{
					cmd.Transaction.Rollback();
					throw new Exception("This username is already registered.");
				}
				if (cmd.Parameters["state_code"].Value.ToString() == "2")
				{
					throw new Exception("The message has been recognized to be spam.");
				}
				else
				{
					cmd.Transaction.Rollback();
					throw new Exception("Unknow statuscode: " + cmd.Parameters["state_code"].Value.ToString());
				}
			}
		}

		public void UpdatePosting(string content, Int64 postID)
		{
			SqlCommand cmd = null;

			cmd = new SqlCommand("UpdatePost", Connection);

			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add(new SqlParameter("content", content));
			cmd.Parameters.Add(new SqlParameter("postID", postID));

			Connection.Open();

			try
			{
				cmd.ExecuteNonQuery();
				Connection.Close();
				Connection.Dispose();
			}
			catch (Exception e)
			{
				Connection.Close();

				throw e;
			}
		}


		public void DeletePost(Int64 postID, Int64 topicID)
		{
			SqlCommand cmd = null;

			cmd = new SqlCommand("DeletePost", Connection);

			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add(new SqlParameter("postID", postID));
			cmd.Parameters.Add(new SqlParameter("topicID", topicID));

			Connection.Open();

			try
			{
				cmd.ExecuteNonQuery();
				Connection.Close();
				Connection.Dispose();
			}
			catch (Exception e)
			{
				Connection.Close();

				throw e;
			}
		}



	}
}

